package com.sxfq.loansupermarket.backsystem.modular.arrival.controller;

import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.util.CommUtils;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerPublicService;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.system.service.IDeptService;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.service.IConsultantService;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.validation.Valid;
import java.io.*;
import java.util.*;

/**
 * @author py
 * @Date 2019年4月19日23:15:54
 */
@Controller
@RequestMapping("/customerUpload")
public class CustomerUploadController extends BaseController {
    @Autowired
    ICustomerService service;

    @Autowired
    ICustomerPublicService customerPublicService;

    @Autowired
    IConsultantService consultantService;

    @Autowired
    private SqlMapper sqlMapper;

    @Autowired
    IReservationsService reservationsService;

    @Autowired
    IDeptService deptService;

    private String PREFIX = "/arrival/customer/";

    @RequestMapping(value = "/customer_import")
    public String to_import(Model model) {
        return PREFIX + "customer_import.html";
    }

    //导入 e看牙
    @RequestMapping(value = "/upload1")
    public String importTestQuestions(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);
                    Cell xh5 = hssfRow.getCell(4);
                    Cell xh6 = hssfRow.getCell(5);
                    Cell xh7 = hssfRow.getCell(6);
                    Cell xh8 = hssfRow.getCell(7);
                    Cell xh9 = hssfRow.getCell(8);
                    Cell xh10 = hssfRow.getCell(9);
                    Cell xh11 = hssfRow.getCell(10);
                    Cell xh12 = hssfRow.getCell(11);
                    Cell xh13 = hssfRow.getCell(12);
                    Cell xh14 = hssfRow.getCell(13);
                    Cell xh15 = hssfRow.getCell(14);
                    Cell xh16 = hssfRow.getCell(15);
                    Cell xh17 = hssfRow.getCell(16);
                    Cell xh18 = hssfRow.getCell(17);
                    Cell xh19 = hssfRow.getCell(18);
                    Cell xh20 = hssfRow.getCell(19);
                    Cell xh21 = hssfRow.getCell(20);
                    Cell xh22 = hssfRow.getCell(21);
                    Cell xh23 = hssfRow.getCell(22);
                    Cell xh24 = hssfRow.getCell(23);
                    Cell xh25 = hssfRow.getCell(24);
                    Cell xh26 = hssfRow.getCell(25);
                    Cell xh27 = hssfRow.getCell(26);
                    Cell xh28 = hssfRow.getCell(27);
                    Cell xh29 = hssfRow.getCell(28);
                    Cell xh30 = hssfRow.getCell(29);
                    Cell xh31 = hssfRow.getCell(30);
                    Cell xh32 = hssfRow.getCell(31);
                    Cell xh33 = hssfRow.getCell(32);
                    Cell xh34 = hssfRow.getCell(33);
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    String xh9s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh12s = "";
                    String xh13s = "";
                    String xh14s = "";
                    String xh15s = "";
                    String xh16s = "";
                    String xh17s = "";
                    String xh18s = "";
                    String xh19s = "";
                    String xh20s = "";
                    String xh21s = "";
                    String xh22s = "";
                    String xh23s = "";
                    String xh24s = "";
                    String xh25s = "";
                    String xh26s = "";
                    String xh27s = "";
                    String xh28s = "";
                    String xh29s = "";
                    String xh30s = "";
                    String xh31s = "";
                    String xh32s = "";
                    String xh33s = "";
                    String xh34s = "";
                    String xh230s="";
                    if ((xh1) == null || ("").equals(xh1)) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2) == null || ("").equals(xh2)) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3) == null || ("").equals(xh3)) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4) == null || ("").equals(xh4)) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }
                    if ((xh5) == null || ("").equals(xh5)) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }
                    if ((xh6) == null || ("").equals(xh6)) {
                        xh6s = "null";
                    } else {
                        xh6s = "'" + xh6 + "'";
                    }
                    if ((xh7) == null || ("").equals(xh7)) {
                        xh7s = "null";
                    } else {
                        xh7s = "'" + xh7 + "'";
                    }
                    if ((xh8) == null || ("").equals(xh8+ "")) {
                        xh8s = "null";
                    } else {
                        xh8s = "'" + xh8 + "'";
                    }
                    if ((xh9) == null || ("").equals(xh9)) {
                        xh9s = "null";
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    if ((xh10) == null || ("").equals(xh10)) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if ((xh11) == null || ("").equals(xh11)) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if ((xh12) == null || ("").equals(xh12+ "")) {
                        xh12s = "null";
                    } else {
                        xh12s = "'" + xh12 + "'";
                    }
                    if ((xh13) == null || ("").equals(xh13)) {
                        xh13s = "null";
                    } else {
                        xh13s = "'" + xh13 + "'";
                    }
                    if ((xh14) == null || ("").equals(xh14)) {
                        xh14s = "null";
                    } else {
                        xh14s = "'" + xh14 + "'";
                    }
                    if ((xh15) == null || ("").equals(xh15+ "")) {
                        xh15s = "null";
                    } else {
                        xh15s = "'" + xh15 + "'";
                    }
                    if ((xh16) == null || ("").equals(xh16+ "")) {
                        xh16s = "null";
                    } else {
                        xh16s = "'" + xh16 + "'";
                    }
                    if ((xh17) == null || ("").equals(xh17+ "")) {
                        xh17s = "null";
                    } else {
                        xh17s = "'" + xh17 + "'";
                    }
                    if ((xh18) == null || ("").equals(xh18)) {
                        xh18s = "null";
                    } else {
                        xh18s = "'" + xh18 + "'";
                    }
                    if ((xh19) == null || ("").equals(xh19)) {
                        xh19s = "null";
                    } else {
                        if ("是".equals((xh19 + ""))) {
                            xh19s = "'已结账'";
                            xh230s = "'已成交'";
                        } else if ("否".equals((xh19 + ""))) {
                            xh19s = "'未开单'";
                            xh230s = "'未成交'";
                        }
                    }
                    //1:预约,2:到店,3:就诊中,4:完成治疗,5:离开,6:已打电话，7:失约
                    if ((xh20 + "") == null || ("").equals((xh20 + ""))) {
                        xh20s = "null";
                    } else {
                        if ("已确认".equals((xh20 + ""))) {
                            xh20s = "'1'";
                        } else if ("已挂号".equals((xh20 + ""))) {
                            xh20s = "'2'";
                        } else if ("治疗中".equals((xh20 + ""))) {
                            xh20s = "'3'";
                        } else if ("治疗完成".equals((xh20 + ""))) {
                            xh20s = "'4'";
                        } else if ("已离开".equals((xh20 + ""))) {
                            xh20s = "'5'";
                        } else if ("失约".equals((xh20 + ""))) {
                            xh20s = "'7'";
                        } else if ("null".equals((xh20 + ""))) {
                            xh20s = "'1'";
                        } else {
                            xh20s = "'5'";
                        }
                    }
                    if ((xh21 + "") == null || ("").equals((xh21 + ""))) {
                        xh21s = "null";
                    } else {
                        xh21s = "'" + xh21 + "'";
                    }
                    if ((xh22 + "") == null || ("").equals((xh22 + ""))) {
                        xh22s = "null";
                    } else {
                        xh22s = "'" + xh22 + "'";
                    }
                    if ((xh23 + "") == null || ("").equals((xh23 + ""))) {
                        xh23s = "null";
                    } else {
                        xh23s = "'" + xh23 + "'";
                    }
                    if ((xh24 + "") == null || ("").equals((xh24 + ""))) {
                        xh24s = "null";
                    } else {
                        xh24s = "'" + xh24 + "'";
                    }
                    if ((xh25 + "") == null || ("").equals((xh25 + ""))) {
                        xh25s = "null";
                    } else {
                        xh25s = "'" + xh25 + "'";
                    }
                    if ((xh26 + "") == null || ("").equals((xh26 + ""))) {
                        xh26s = "null";
                    } else {
                        xh26s = "'" + xh26 + "'";
                    }
                    if ((xh27 + "") == null || ("").equals((xh27 + ""))) {
                        xh27s = "null";
                    } else {
                        xh27s = "'" + xh27 + "'";
                    }
                    if ((xh28 + "") == null || ("").equals((xh28 + ""))) {
                        xh28s = "null";
                    } else {
                        xh28s = "'" + xh28 + "'";
                    }
                    if ((xh29 + "") == null || ("").equals((xh29 + ""))) {
                        xh29s = "null";
                    } else {
                        xh29s = "'" + xh29 + "'";
                    }
                    if ((xh30 + "") == null || ("").equals((xh30 + ""))) {
                        xh30s = "null";
                    } else {
                        xh30s = "'" + xh30 + "'";
                    }
                    if ((xh31 + "") == null || ("").equals((xh31 + ""))) {
                        xh31s = "null";
                    } else {
                        xh31s = "'" + xh31 + "'";
                    }
                    if ((xh32 + "") == null || ("").equals((xh32 + ""))) {
                        xh30s = "null";
                    } else {
                        xh32s = "'" + xh32 + "'";
                    }
                    if ((xh33 + "") == null || ("").equals((xh33 + ""))) {
                        xh33s = "null";
                    } else {
                        xh33s = "'" + xh33 + "'";
                    }
                    if ((xh34 + "") == null || ("").equals((xh34 + ""))) {
                        xh34s = "null";
                    } else {
                        xh34s = "'" + xh34 + "'";
                    }
                    /*String ageRange = "";
                    if (!"null".equals(xh6s)) {
                        for (int i = 0; i < 11; i++) {
                            if (Integer.parseInt(xh6s.substring(1, xh6s.length() - 3)) >= (i * 10) &&
                                    Integer.parseInt(xh6s.substring(1, xh6s.length() - 3)) <= (i * 10 + 9)) {
                                ageRange = (i * 10) + "-" + (i * 10 + 9);
                            }
                        }
                    }*/
                    //导入excel到店时间升序
                    String customerStatus = "";
                    if (!"null".equals(xh16s) ) {  //挂号时间不为空
                        if("null".equals(xh12s)){
                            xh12s=xh16s;
                        }
                        if (!(xh12s.substring(1, 11)).equals(xh16s.substring(1, 11))) {//挂号时间等于初到时间
                            String sql2 = "SELECT  id " +
                                    " from " + ShiroKit.getUser().getCustomer_info() + " a where left(a.arrival_time,10)<'" + xh16s.substring(1, 11) + "' " +
                                    " and deal_status= '已结账' and medical_record_number=" + xh2s + " ";  //查之前这个人
                            List<CustomerInfo> list2 = sqlMapper.selectList(sql2, CustomerInfo.class);
                            if (list2.size() > 0) {   //之前成交了
                                customerStatus = "老客";
                            } else {
                                customerStatus = "新客多次";
                            }
                        } else {
                            customerStatus = "新客首次";
                        }
                    } else {    //挂号时间为空
                        customerStatus = "预约";
                    }
//                    //生成预约客户id
//                    String customerid = CommUtils.getUUID();
//                    String tableHead = ShiroKit.getUser().getAbbreviation();
//                    customerid = customerid + tableHead;
//                    Random rand = new Random();
//                    int randomNumber = rand.nextInt(999999);
//                    customerid = customerid + randomNumber;

                    //生成预约id
                    /* String reservationsid = "";
                    Random rand2 = new Random();
                    int randomNumber2 = rand2.nextInt(999999);
                    reservationsid = "sme" + xh2s;
                    String selectsql2 = "select reservations_id from " + ShiroKit.getUser().getCustomer_info() + " where medical_record_number=" + xh1s + " limit 0,1 ;";
                    CustomerInfo customerInfosList2 = sqlMapper.selectOne(selectsql2, CustomerInfo.class);
                    if (customerInfosList2 != null) {
                        reservationsid = customerInfosList2.getReservationsId();
                    } */

                    String sql = "insert into " + ShiroKit.getUser().getCustomer_info() + " " +
                            "(reservations_id,id22, medical_record_number,customer_name,yellow_number,customer_sex,customer_age,customer_phone,filing_time," +
                            "one_channel,two_channel,thr_channel,one_medium,two_medium,thr_medium," +
                            "first_visit_date,appointment_pay_service,entry_clerk,archiving,appointment_time,arrival_time," +
                            "customer_status,doctor,treatment_unit," +
                            "deal_status,isdeal_status,scarchiving,field_tc," +
                            " age_range,arrival_store,arrival_status,szm) VALUES " +
                            "( " + xh2s + ",'8月3导入万达', " + xh2s + "," + xh3s + "," + xh4s + ", " + xh5s + " " +
                            ", " + xh6s + "," + xh7s + "," + xh8s.substring(0, 11) + "' " +
                            ", " + xh9s + " , " + xh10s + " , " + xh11s + ", " + xh9s + " , " + xh10s + " , " + xh11s
                            + " , " + xh12s + " , " + xh13s + " , " + xh14s + " , " + xh14s + " , " + xh15s + " , " + xh16s
                            + " , '"+ customerStatus + "', " + xh17s + ", " + xh18s
                            + "," + xh19s + "," + xh230s + "," + xh21s + "," + xh22s
                            + " ,'' ,'万达店'," + xh20s + ",firstconcats(" + xh3s + "));";
                    sqlMapper.insert(sql);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return "/testClose.html";
        } else {
            return "/testClose.html";
        }
    }


    /**
     * 导入 牙医管家
     */
    @RequestMapping(value = "/upload")
    public String importTestQuestions1(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);
                    Cell xh5 = hssfRow.getCell(4);
                    Cell xh6 = hssfRow.getCell(5);
                    Cell xh7 = hssfRow.getCell(6);
                    Cell xh8 = hssfRow.getCell(7);
                    Cell xh9 = hssfRow.getCell(8);
                    Cell xh10 = hssfRow.getCell(9);
                    Cell xh11 = hssfRow.getCell(10);
                    Cell xh12 = hssfRow.getCell(11);
                    Cell xh13 = hssfRow.getCell(12);
                    Cell xh14 = hssfRow.getCell(13);
                    Cell xh15 = hssfRow.getCell(14);
                    Cell xh16 = hssfRow.getCell(15);
                    Cell xh17 = hssfRow.getCell(16);
                    Cell xh18 = hssfRow.getCell(17);
                    Cell xh19 = hssfRow.getCell(18);
                    Cell xh20 = hssfRow.getCell(19);
                    Cell xh21 = hssfRow.getCell(20);
                    Cell xh22 = hssfRow.getCell(21);
                    Cell xh23 = hssfRow.getCell(22);
                    Cell xh24 = hssfRow.getCell(23);
                    Cell xh25 = hssfRow.getCell(24);
                    Cell xh26 = hssfRow.getCell(25);
                    Cell xh27 = hssfRow.getCell(26);
                    Cell xh28 = hssfRow.getCell(27);
                    Cell xh29 = hssfRow.getCell(28);
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    String xh9s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh12s = "";
                    String xh13s = "";
                    String xh14s = "";
                    String xh15s = "";
                    String xh16s = "";
                    String xh17s = "";
                    String xh18s = "";
                    String xh19s = "";
                    String xh20s = "";
                    String xh21s = "";
                    String xh22s = "";
                    String xh23s = "";
                    String xh24s = "";
                    String xh25s = "";
                    String xh26s = "";
                    String xh27s = "";
                    String xh28s = "";
                    String xh29s = "";
                    if ((xh1) == null || ("").equals(xh1)) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2) == null || ("").equals(xh2)) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3) == null || ("").equals(xh3)) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4) == null || ("").equals(xh4)) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }
                    if ((xh5) == null || ("").equals(xh5)) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }
                    if ((xh6) == null || ("").equals(xh6.toString())) {
                        xh6s = "null";
                    } else {
                        xh6s = "'" + xh6 + "'";
                    }
                    if ((xh7) == null || ("").equals(xh7)) {
                        xh7s = "null";
                    } else {
                        xh7s = "'" + xh7 + "'";
                    }
                    if ((xh8) == null || ("").equals(xh8)) {
                        xh8s = "null";
                    } else {
                        xh8s = "'" + xh8 + "'";
                    }
                    if ((xh9) == null || ("").equals(xh9)) {
                        xh9s = "null";
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    if ((xh10) == null || ("").equals(xh10)) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if ((xh11) == null || ("").equals(xh11)) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if ((xh12) == null || ("").equals(xh12.toString())) {
                        xh12s = "null";
                    } else {
                        xh12s = "'" + xh12 + "'";
                    }
                    if ((xh13) == null || ("").equals((xh13.toString()))) {
                        xh13s = "null";
                    } else {
                        xh13s = "'" + xh13 + "'";
                    }
                    if ((xh14) == null || ("").equals((xh14.toString()))) {
                        xh14s = "null";
                    } else {
                        xh14s = "'" + xh14 + "'";
                    }
                    if ((xh15) == null || ("").equals((xh15.toString()))) {
                        xh15s = "null";
                    } else {
                        xh15s = "'" + xh15 + "'";
                    }
                    if ((xh16) == null || ("").equals((xh16.toString()))) {
                        xh16s = "null";
                    } else {
                        xh16s = "'" + xh16 + "'";
                    }
                    if ((xh17) == null || ("").equals((xh17))) {
                        xh17s = "null";
                    } else {
                        xh17s = "'" + xh17 + "'";
                    }
                    if ((xh18) == null || ("").equals((xh18))) {
                        xh18s = "null";
                    } else {
                        xh18s = "'" + xh18 + "'";
                    }
                    if ((xh19) == null || ("").equals((xh19))) {
                        xh19s = "null";
                    } else {
                        xh19s = "'" + xh19 + "'";
                    }
                    if ((xh20) == null || ("").equals((xh20))) {
                        xh20s = "null";
                    } else {
                        xh20s = "'" + xh20 + "'";
                    }
                    if ((xh21) == null || ("").equals((xh21))) {
                        xh21s = "null";
                    } else {
                        xh21s = "'" + xh21 + "'";
                    }
                    if ((xh22) == null || ("").equals((xh22))) {
                        xh22s = "null";
                    } else {
                        xh22s = "'" + xh22 + "'";
                    }
                    if ((xh23) == null || ("").equals((xh23))) {
                        xh23s = "null";
                    } else {
                        xh23s = "'" + xh23 + "'";
                    }
                    if ((xh24) == null || ("").equals((xh24))) {
                        xh24s = "null";
                    } else {
                        xh24s = "'" + xh24 + "'";
                    }
                    if ((xh25) == null || ("").equals((xh25))) {
                        xh25s = "null";
                    } else {
                        xh25s = "'" + xh25 + "'";
                    }
                    if ((xh26) == null || ("").equals((xh26))) {
                        xh26s = "null";
                    } else {
                        xh26s = "'" + xh26 + "'";
                    }
                    if ((xh27) == null || ("").equals((xh27))) {
                        xh27s = "null";
                    } else {
                        xh27s = "'" + xh27 + "'";
                    }
                    if ((xh28) == null || ("").equals((xh28))) {
                        xh28s = "null";
                    } else {
                        xh28s = "'" + xh28 + "'";
                    }
                    if ((xh29) == null || ("").equals((xh29))) {
                        xh29s = "null";
                    } else {
                        xh29s = "'" + xh29 + "'";
                    }
                    //导入excel到店时间升序
                    String customerStatus = "";
                    if (!"null".equals(xh15s) || !"".equals(xh15s.toString())) {  //挂号时间不为空
                        if("null".equals(xh12s) || "".equals(xh12s.toString())){
                            xh12s=xh15s;
                        }
                        if (!(xh12s.substring(1, 11)).equals(xh15s.substring(1, 11))) {//挂号时间等于初到时间
                            String sql2 = "SELECT  id " +
                                    " from " + ShiroKit.getUser().getCustomer_info() + " a where left(a.arrival_time,10)<'" + xh15s.substring(1, 11) + "' " +
                                    " and deal_status= '已结账' and medical_record_number=" + xh3s + " limit 2";  //查之前这个人
                            List<CustomerInfo> list2 = sqlMapper.selectList(sql2, CustomerInfo.class);
                            if (list2.size() > 0) {   //之前成交了
                                customerStatus = "老客";
                            } else {
                                customerStatus = "新客多次";
                            }
                        } else {
                            customerStatus = "新客首次";
                        }
                    } else {    //挂号时间为空
                        customerStatus = "预约";
                    }
                    //生成预约客户id
                    String customerid = CommUtils.getUUID();
                    String tableHead = ShiroKit.getUser().getAbbreviation();
                    customerid = customerid + tableHead;
                    Random rand = new Random();
                    int randomNumber = rand.nextInt(999999);
                    customerid = customerid + randomNumber;

                    String sql = "insert into " + ShiroKit.getUser().getCustomer_info() + " " +
                            "(id,id22,reservations_id, medical_record_number,customer_name,customer_sex,birthday,customer_phone," +
                            "one_channel,one_medium,first_visit_date,appointment_pay_service,remarks_reservation," +
                            "start_time,end_time,arrival_time,filing_time,doctor,archiving,field_tc,deal_status,phone_shar_ship," +
                            "country,cities,address," +
                            " customer_status,arrival_store,szm) VALUES " +
                            "('"+customerid+"','2020年01月18日导入1健吉', " + xh1s + ","+ xh3s + "," + xh4s + ", " + xh8s + "," + xh6s + "," + xh5s + " " +
                            ", " + xh9s + ", " + xh9s + "," + xh12s + "," + xh21s  + "," + xh22s
                            + ","+ xh13s+" , "+ xh14s+" , "+ xh15s + ", "+ xh16s+ ", " + xh23s + ","+ xh24s + "," + xh25s + "," + xh26s+"," + xh27s
                            + "," + xh17s + "," + xh18s+"," + xh19s
                            + ",'" + customerStatus + "' ,'健吉店',firstconcats(" + xh4s + "));";
                    sqlMapper.insert(sql);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return "/testClose.html";
        } else {
            return "/testClose.html";
        }
    }

    /**
     * 导入修改市场渠道
     */
    @RequestMapping(value = "/upload2")
    public String importTestQuestions2(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);
                    Cell xh5 = hssfRow.getCell(4);
                    Cell xh6 = hssfRow.getCell(5);
                    Cell xh7 = hssfRow.getCell(6);
                    Cell xh8 = hssfRow.getCell(7);
                    Cell xh9 = hssfRow.getCell(8);
                    Cell xh10 = hssfRow.getCell(9);
                    Cell xh11 = hssfRow.getCell(10);
                    Cell xh12 = hssfRow.getCell(11);
                    Cell xh13 = hssfRow.getCell(12);
                    Cell xh14 = hssfRow.getCell(13);
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    String xh9s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh12s = "";
                    String xh13s = "";
                    String xh14s = "";
                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = " is null";
                    } else {
                        xh1s = "='" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = " is null";
                    } else {
                        xh2s = "='" + xh2 + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = " is null";
                    } else {
                        xh3s = "='" + xh3 + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = " is null";
                    } else {
                        xh4s = "='" + xh4 + "'";
                    }
                    if ((xh5 + "") == null || ("").equals((xh5 + ""))) {
                        xh5s = " is null";
                    } else {
                        xh5s = "='" + xh5 + "'";
                    }
                    if ((xh6 + "") == null || ("").equals((xh6 + ""))) {
                        xh6s = " is null";
                    } else {
                        xh6s = "='" + xh6 + "'";
                    }
                    if ((xh7 + "") == null || ("").equals((xh7 + ""))) {
                        xh7s = " is null";
                    } else {
                        xh7s = "='" + xh7 + "'";
                    }
                    if ((xh8 + "") == null || ("").equals((xh8 + ""))) {
                        xh8s = " is null";
                    } else {
                        xh8s = "='" + xh8 + "'";
                    }
                    if ((xh9 + "") == null || ("").equals((xh9 + ""))) {
                        xh9s = "null";
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    if ((xh10 + "") == null || ("").equals((xh10 + ""))) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if ((xh11 + "") == null || ("").equals((xh11 + ""))) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if ((xh12 + "") == null || ("").equals((xh12 + ""))) {
                        xh12s = "null";
                    } else {
                        xh12s = "'" + xh12 + "'";
                    }
                    if ((xh13 + "") == null || ("").equals((xh13 + ""))) {
                        xh13s = "null";
                    } else {
                        xh13s = "'" + xh13 + "'";
                    }
                    if ((xh14 + "") == null || ("").equals((xh14 + ""))) {
                        xh14s = "null";
                    } else {
                        xh14s = "'" + xh14 + "'";
                    }
                    String sql = "update reservations_info set" +
                            " one_channel="+xh9s+" , two_channel="+xh10s+", thr_channel="+xh11s+", " +
                            " one_medium="+xh12s+"  , two_medium="+xh13s+", thr_medium="+xh14s+"  "+
                            " where department"+xh1s+" and archiving"+xh2s+" " +
                            " and one_channel"+xh3s+" and two_channel"+xh4s+" and thr_channel"+xh5s+" " +
                            " and one_medium"+xh6s+"  and two_medium"+xh7s+" and thr_medium"+xh8s+"  ";
                    sqlMapper.update(sql);
                    String sql2 = "update reservations_infosc set" +
                            " one_channel="+xh9s+" , two_channel="+xh10s+", thr_channel="+xh11s+", " +
                            " one_medium="+xh12s+"  , two_medium="+xh13s+", thr_medium="+xh14s+"  "+
                            " where department"+xh1s+" and archiving"+xh2s+" " +
                            " and one_channel"+xh3s+" and two_channel"+xh4s+" and thr_channel"+xh5s+" " +
                            " and one_medium"+xh6s+"  and two_medium"+xh7s+" and thr_medium"+xh8s+"  ";
                    sqlMapper.update(sql2);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return "/testClose.html";
        } else {
            return "/testClose.html";
        }
    }


}